<?php

///////////////////////////////////////////////////////////////////////////////
/**
 * IConnector implementation for the sqlsrv (Microsoft SQL Server 2005 Driver
 * for PHP) database extension.
 *
 * System requirements:
 * <ul>
 * <li>PHP 5</li>
 * <li>The {@link http://www.codeplex.com/SQL2K5PHP Microsoft SQL Server 2005 Driver for PHP}</li>
 * </ul>
 *
 * This library is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * The Connector library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * {@link http://www.gnu.org/copyleft/lesser.html GNU Lesser General Public License}
 * for more details.
 *
 * @author Per Egil Roksvaag
 * @copyright 2009 Per Egil Roksvaag
 * @license http://www.gnu.org/copyleft/lesser.html GNU Lesser General Public License
 * @package connector
 * @version 2.0.0
 */

///////////////////////////////////////////////////////////////////////////////
/**
 * Include the parent class.
 */
require_once("BaseConnector.php");

/**
 * IConnector implementation for Microsoft SQL Server 2005 Driver for PHP.
 * @package connector
 */
class SqlsrvConnector extends BaseConnector implements IConnector
{
	///////////////////////////////////////////////////////////////////////////
	/**
	 * @var array Multibyte character encodings matching the MSSQL unicode format.
	 */
	static protected $unicode = array("UCS-2LE", "UTF-16LE");

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Open a connection to a MSSQL Server 2005 or higher and set global options.
	 *
	 * @param array $connection An associated array of connection settings, like host and user name.
	 * @param array $options An associated array of global options for the resulting instance.
	 * @return SqlsrvConnector
	 */
	public function SqlsrvConnector($connection, $options = array())
	{
		if($this->open($connection))
		{
			$this->options[self::PARAM_QUERIES] = true;
			$this->options[self::PARAM_NAMED] = false;
			$this->options[self::PARAM_PREFIX] = "?";
			parent::BaseConnector($options);
			return $this;
		}
		if($this->lookup(self::LOG_ERROR, $options))
		{
			$log = "SqlsrvConnector error: Connection failed in ";
			error_log($log.$_SERVER["SCRIPT_FILENAME"]);
		}
		if($this->lookup(self::THROW_EXCEPTION, $options))
		{
			throw new ConnectorException(ConnectorException::CONNECTION_FAILED,
				array($this->getError()));
		}
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Open a database connection.
	 * @param array $connection An associated array of connection settings, like host and user name.
	 * @return bool <var>true</var>, if a database connection was successfully opened, <var>false</var> otherwise.
	 */
	protected function open($connection)
	{
		$hostname = $this->lookup(self::CONN_HOSTNAME, $connection);
		$database = $this->lookup(self::CONN_DATABASE, $connection);
		$username = $this->lookup(self::CONN_USERNAME, $connection);
		$password = $this->lookup(self::CONN_PASSWORD, $connection);

		$port = $this->lookup(self::CONN_PORT, $connection);
		$pool = $this->lookup(self::CONN_POOL, $connection, true);
		$native = $this->lookup(self::CONN_NATIVE, $connection, array());

		$database && $native["Database"] = $database;
		$username && $native["UID"] = $username;
		$password && $native["PWD"] = $password;
		$native["ConnectionPooling"] = $pool;
		$port && $hostname.= ",".$port;

		$this->conn = sqlsrv_connect($hostname, $native);
		return is_resource($this->conn);
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Send a SQL SELECT query to the database and get the query result.
	 *
	 * @see IConnector::select().
	 * @param string $query A SQL query to execute on a database.
	 * @param array $param An associated array of values to be used in the $query.
	 * @param array $map An array of type definitions for the <var>$param</var> values.
	 * @param array $options An associated array of options, see the {@tutorial connector.pkg#options.element}.
	 * @throws ConnectorException when $param doesn't match the type definition $map.
	 * @return array The query result as a table (array of associated arrays).
	 */
	public function select($query, $param = array(), $map = array(), $options = array())
	{
		$param = TypeValidator::check($param, $map);
		$query = $this->bind($query, $param, $stack, $options);
		$query = $this->build($query, $options);
		$hash = $this->getHash($query, $stack, $options);

		if($this->lookup(self::LOG_DEBUG, $options))
		{
			$log1 = "SqlsrvConnector debug: Select query in ";
			$log2 = $stack ? LB."Params: ".@implode(", ", $stack) : "";
			error_log($log1.$_SERVER["SCRIPT_FILENAME"].LB.$query.$log2);
		}
		if($this->getCache($hash, $table, $options))
		{
			return $table;
		}
		if($stmt = sqlsrv_query($this->conn, $query, $stack))
		{
			do $table[] = $this->fetch($stmt, $options);
			while(sqlsrv_next_result($stmt));
			sqlsrv_free_stmt($stmt);

			if(count($table) == 1) $table = current($table);
			$this->setCache($hash, $table, $options);
			return $table;
		}
		if($this->lookup(self::LOG_ERROR, $options))
		{
			$log1 = "SqlsrvConnector error: Select query in ";
			$log2 = $stack ? LB."Params: ".@implode(", ", $stack) : "";
			error_log($log1.$_SERVER["SCRIPT_FILENAME"].LB.$query.$log2);
			error_log("SqlsrvConnector error: ".sqlsrv_errors());
		}
		if($this->lookup(self::THROW_EXCEPTION, $options))
		{
			throw new ConnectorException(ConnectorException::QUERY_FAILED,
				array($this->getError(), $query, $stack ? implode(", ", $stack) : ""));
		}
		return false;
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Send a SQL INSERT query to the database and get the IDENTITY ID
	 * generated from the last INSERT operation (if any).
	 *
	 * @see IConnector::insert().
	 * @param string $query A SQL query to execute on a database.
	 * @param array $param An associated array of values to be used in the $query.
	 * @param array $map An array of type definitions for the <var>$param</var> values.
	 * @param array $options An associated array of options, see the {@tutorial connector.pkg#options.element}.
	 * @throws ConnectorException when $param doesn't match the type definition $map.
	 * @return int The IDENTITY ID of the last inserted row.
	 */
	public function insert($query, $param = array(), $map = array(), $options = array())
	{
		$param = TypeValidator::check($param, $map);
		$query = $this->bind($query, $param, $stack, $options);
		$query = rtrim($query, ";").";".LB."SELECT SCOPE_IDENTITY();";

		if($this->lookup(self::LOG_DEBUG, $options))
		{
			$log1 = "SqlsrvConnector debug: Insert query in ";
			$log2 = $stack ? LB."Params: ".@implode(", ", $stack) : "";
			error_log($log1.$_SERVER["SCRIPT_FILENAME"].LB.$query.$log2);
		}
		if($stmt = sqlsrv_query($this->conn, $query, $stack))
		{
			while(sqlsrv_next_result($stmt))
			{
				$row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC);
			}
			sqlsrv_free_stmt($stmt);
			$key = is_array($row) ? current($row) : null;
			return is_numeric($key) ? (int)$key : $key;
		}
		if($this->lookup(self::LOG_ERROR, $options))
		{
			$log1 = "SqlsrvConnector error: Insert query in ";
			$log2 = $stack ? LB."Params: ".@implode(", ", $stack) : "";
			error_log($log1.$_SERVER["SCRIPT_FILENAME"].LB.$query.$log2);
			error_log("SqlsrvConnector error: ".sqlsrv_errors());
		}
		if($this->lookup(self::THROW_EXCEPTION, $options))
		{
			throw new ConnectorException(ConnectorException::QUERY_FAILED,
				array($this->getError(), $query, $stack ? implode(", ", $stack) : ""));
		}
		return false;
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Send a SQL UPDATE query to the database and get the number of rows
	 * updates by the query.
	 *
	 * @see IConnector::update().
	 * @param string $query A SQL query to execute on a database.
	 * @param array $param An associated array of values to be used in the $query.
	 * @param array $map An array of type definitions for the <var>$param</var> values.
	 * @param array $options An associated array of options, see the {@tutorial connector.pkg#options.element}.
	 * @throws ConnectorException when $param doesn't match the type definition $map.
	 * @return int The number of rows updates by the query.
	 */
	public function update($query, $param = array(), $map = array(), $options = array())
	{
		$param = TypeValidator::check($param, $map);
		$query = $this->bind($query, $param, $stack, $options);

		if($this->lookup(self::LOG_DEBUG, $options))
		{
			$log1 = "SqlsrvConnector debug: Update query in ";
			$log2 = $stack ? LB."Params: ".@implode(", ", $stack) : "";
			error_log($log1.$_SERVER["SCRIPT_FILENAME"].LB.$query.$log2);
		}
		if($stmt = sqlsrv_query($this->conn, $query, $stack))
		{
			$result = sqlsrv_rows_affected($stmt);
			sqlsrv_free_stmt($stmt);
			return $result;
		}
		if($this->lookup(self::LOG_ERROR, $options))
		{
			$log1 = "SqlsrvConnector error: Update query in ";
			$log2 = $stack ? LB."Params: ".@implode(", ", $stack) : "";
			error_log($log1.$_SERVER["SCRIPT_FILENAME"].LB.$query.$log2);
			error_log("SqlsrvConnector error: ".sqlsrv_errors());
		}
		if($this->lookup(self::THROW_EXCEPTION, $options))
		{
			throw new ConnectorException(ConnectorException::QUERY_FAILED,
				array($this->getError(), $query, $stack ? implode(", ", $stack) : ""));
		}
		return false;
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Send a SQL DELETE query to the database and get the number of rows
	 * deleted by the query.
	 *
	 * @see IConnector::delete().
	 * @param string $query A SQL query to execute on a database.
	 * @param array $param An associated array of values to be used in the $query.
	 * @param array $map An array of type definitions for the <var>$param</var> values.
	 * @param array $options An associated array of options, see the {@tutorial connector.pkg#options.element}.
	 * @throws ConnectorException when $param doesn't match the type definition $map.
	 * @return int The number of rows deleted by the query.
	 */
	public function delete($query, $param = array(), $map = array(), $options = array())
	{
		$param = TypeValidator::check($param, $map);
		$query = $this->bind($query, $param, $stack, $options);

		if($this->lookup(self::LOG_DEBUG, $options))
		{
			$log1 = "SqlsrvConnector debug: Delete query in ";
			$log2 = $stack ? LB."Params: ".@implode(", ", $stack) : "";
			error_log($log1.$_SERVER["SCRIPT_FILENAME"].LB.$query.$log2);
		}
		if($stmt = sqlsrv_query($this->conn, $query, $stack))
		{
			$result = sqlsrv_rows_affected($stmt);
			sqlsrv_free_stmt($stmt);
			return $result;
		}
		if($this->lookup(self::LOG_ERROR, $options))
		{
			$log1 = "SqlsrvConnector error: Delete query in ";
			$log2 = $stack ? LB."Params: ".@implode(", ", $stack) : "";
			error_log($log1.$_SERVER["SCRIPT_FILENAME"].LB.$query.$log2);
			error_log("SqlsrvConnector error: ".sqlsrv_errors());
		}
		if($this->lookup(self::THROW_EXCEPTION, $options))
		{
			throw new ConnectorException(ConnectorException::QUERY_FAILED,
				array($this->getError(), $query, $stack ? implode(", ", $stack) : ""));
		}
		return false;
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Begins a transaction on the current connection.
	 * The current transaction includes all statements on the connection that
	 * were executed after the call to transaction() and before any calls
	 * to rollback() or commit().
	 *
	 * @see IConnector::transaction().
	 * @param array $options An associated array of options, see the {@tutorial connector.pkg#options.element}.
	 * @return bool true if the transaction was successfully begun, false otherwise.
	 */
	public function transaction($options = array())
	{
		if($this->lookup(self::LOG_DEBUG, $options))
		{
			$log = "SqlsrvConnector debug: Begin transaction in ";
			error_log($log.$_SERVER["SCRIPT_FILENAME"]);
		}
		if(sqlsrv_begin_transaction($this->conn))
		{
			return true;
		}
		if($this->lookup(self::LOG_ERROR, $options))
		{
			$log = "SqlsrvConnector error: Begin transaction in ";
			error_log($log.$_SERVER["SCRIPT_FILENAME"]);
			error_log("SqlsrvConnector error: ".sqlsrv_errors());
		}
		if($this->lookup(self::THROW_EXCEPTION, $options))
		{
			throw new ConnectorException(ConnectorException::TRANSACTION_FAILED,
				array($this->getError()));
		}
	}
	
	///////////////////////////////////////////////////////////////////////////
	/**
	 * Commits the current transaction on the current connection.
	 * The current transaction includes all statements on the connection that
	 * were executed after the call to transaction() and before any calls
	 * to rollback() or commit().
	 *
	 * @see IConnector::commit().
	 * @param array $options An associated array of options, see the {@tutorial connector.pkg#options.element}.
	 * @return bool true if the transaction was successfully committed, false otherwise.
	 */
	public function commit($options = array())
	{
		if($this->lookup(self::LOG_DEBUG, $options))
		{
			$log = "SqlsrvConnector debug: Commit in ";
			error_log($log.$_SERVER["SCRIPT_FILENAME"]);
		}
		if(sqlsrv_commit($this->conn))
		{
			return true;
		}
		if($this->lookup(self::LOG_ERROR, $options))
		{
			$log = "SqlsrvConnector error: Commit in ";
			error_log($log.$_SERVER["SCRIPT_FILENAME"]);
			error_log("SqlsrvConnector error: ".sqlsrv_errors());
		}
		if($this->lookup(self::THROW_EXCEPTION, $options))
		{
			throw new ConnectorException(ConnectorException::TRANSACTION_FAILED,
				array($this->getError()));
		}
	}
	
	///////////////////////////////////////////////////////////////////////////
	/**
	 * Rolls back the current transaction on the current connection. 
	 * The current transaction includes all statements on the connection that
	 * were executed after the call to transaction() and before any calls
	 * to rollback() or commit().
	 *
	 * @see IConnector::rollback().
	 * @param array $options An associated array of options, see the {@tutorial connector.pkg#options.element}.
	 * @return bool true if the transaction was successfully rolled back, false otherwise.
	 */
	public function rollback($options = array())
	{
		if($this->lookup(self::LOG_DEBUG, $options))
		{
			$log = "SqlsrvConnector debug: Rollback in ";
			error_log($log.$_SERVER["SCRIPT_FILENAME"]);
		}
		if(sqlsrv_rollback($this->conn))
		{
			return true;
		}
		if($this->lookup(self::LOG_ERROR, $options))
		{
			$log = "SqlsrvConnector error: Rollback in ";
			error_log($log.$_SERVER["SCRIPT_FILENAME"]);
			error_log("SqlsrvConnector error: ".sqlsrv_errors());
		}
		if($this->lookup(self::THROW_EXCEPTION, $options))
		{
			throw new ConnectorException(ConnectorException::TRANSACTION_FAILED,
				array($this->getError()));
		}
	}
	
	///////////////////////////////////////////////////////////////////////////
	/**
	 * MSSQL unicode strings are converted to the special sqlsrv format.
	 *
	 * @param string $value The parameter to replace the query placeholder.
	 * @param array &$stack An array of values for use in parameterized queries.
	 * @param array $options An associated array of options.
	 * @return string A valid SQL string or placeholder.
	 */
	protected function castString($value, $name, &$stack, $options = array())
	{
		$charset = $this->lookup(self::CHAR_DATABASE, $options);
		$parameterized = $this->lookup(self::PARAM_QUERIES, $options);

		if($parameterized && in_array($charset, self::$unicode))
		{
			$value = $this->strStrip($value, $options);
			$value = $this->strEncode($value, $options);
			$value = array($value, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY), SQLSRV_SQLTYPE_NVARCHAR('max'));
			return $this->push($value, $name, $stack, $options);
		}
		else
		{
			return parent::castString($value, $name, $stack, $options);
		}
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Add a TOP x statement to a SQL SELECT query when the
	 * {@link IConnector::RESULT_LENGTH} is set.
	 *
	 * @param string $query A SQL query to execute on a database.
	 * @param array $options An associated array of options.
	 * @return string A modified SQL query.
	 */
	protected function build($query, $options = array())
	{
		$offset = $this->lookup(self::RESULT_OFFSET, $options, 0);
		$length = $this->lookup(self::RESULT_LENGTH, $options);
						
		if($length !== null)
		{
			$number = $offset + $length;
			$pattern = "/\A(SELECT)([\s]+(ALL|DISTINCT))?\b/i";
			$query = preg_replace($pattern, "$0 TOP {$number}", $query);
		}
		return $query;
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Fetch multiple rows of a query result.
	 *
	 * If the {@link IConnector::RESULT_LENGTH} or {@link IConnector::RESULT_OFFSET}
	 * options are set, some rows are omitted from the beginning and/or the end
	 * of the query result.
	 * If the {@link IConnector::RESULT_KEY_FIELD} option is set, the
	 * resulting table is an <b>associated</b> array of rows.
	 *
	 * @param resource $stmt A statement resource corresponding to an executed statement.
	 * @param array $options An associated array of options.
	 * @return array The query result as a table (array of associated arrays).
	 */
	protected function fetch($stmt, $options = array())
	{
		return in_array($this->lookup(self::CHAR_DATABASE, $options), self::$unicode)
			? $this->fetchDoublebyte($stmt, $options)
			: $this->fetchSinglebyte($stmt, $options);
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Fetch multiple rows of a query result with single byte string encoding.
	 *
	 * If the {@link IConnector::RESULT_LENGTH} or {@link IConnector::RESULT_OFFSET}
	 * options are set, some rows are omitted from the beginning and/or the end
	 * of the query result.
	 * If the {@link IConnector::RESULT_KEY_FIELD} option is set, the
	 * resulting table is an <b>associated</b> array of rows.
	 *
	 * @param resource $stmt A statement resource corresponding to an executed statement.
	 * @param array $options An associated array of options.
	 * @return array The query result as a table (array of associated arrays).
	 */
	protected function fetchSinglebyte($stmt, $options = array())
	{
		$key = $this->lookup(self::RESULT_KEY_FIELD, $options);
		$offset = $this->lookup(self::RESULT_OFFSET, $options, 0);
		$length = $this->lookup(self::RESULT_LENGTH, $options);
		$number = is_null($length) ? PHP_INT_MAX : $length + $offset;
		$table = array();
		$index = 0;

		while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
		{
			if($number <= $index) break;
			if($offset > $index++) continue;
			$row = $this->strDecode($row, $options);
			$key ? $table[$row[$key]] = $row : $table[] = $row;
		}
		return $table;
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Fetch multiple rows of a query result with double byte string encoding.
	 *
	 * If the {@link IConnector::RESULT_LENGTH} or {@link IConnector::RESULT_OFFSET}
	 * options are set, some rows are omitted from the beginning and/or the end
	 * of the query result.
	 * If the {@link IConnector::RESULT_KEY_FIELD} option is set, the
	 * resulting table is an <b>associated</b> array of rows.
	 *
	 * WARNING: To override the default PHP types, binary strings are fetched
	 * with the sqlsrv_fetch/sqlsrv_get_field functions. This combination is
	 * REALLY slow. sqlsrv_fetch_array is about 100 times faster.
	 *
	 * @param resource $stmt A statement resource corresponding to an executed statement.
	 * @param array $options An associated array of options.
	 * @return array The query result as a table (array of associated arrays).
	 */
	protected function fetchDoublebyte($stmt, $options = array())
	{
		$key = $this->lookup(self::RESULT_KEY_FIELD, $options);
		$offset = $this->lookup(self::RESULT_OFFSET, $options);
		$length = $this->lookup(self::RESULT_LENGTH, $options);
		$number = is_null($length) ? PHP_INT_MAX : $length + $offset;
		$meta = sqlsrv_field_metadata($stmt);
		$table = array();
		$index = 0;

		while(sqlsrv_fetch($stmt))
		{
			if($number <= $index) break;
			if($offset > $index++) continue;
			$len = count($meta);
			$row = array();

			for($i = 0; $i < $len; $i++)
			{
				$name = $meta[$i]["Name"];
				$type = $meta[$i]["Type"];

				if($type <= -8)
				{
					$value = sqlsrv_get_field($stmt, $i, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY));
					$value = $this->strDecode($value, $options);
					$row[$name] = $value;
				}
				else
				{
					$row[$name] = sqlsrv_get_field($stmt, $i);
				}
			}
			$key ? $table[$row[$key]] = $row : $table[] = $row;
		}
		return $table;
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Format the db driver errors to an error message string.
	 * @return string
	 */
	protected function getError()
	{
		$result = "";
		$error = sqlsrv_errors();
		
		foreach($error as $part)
		{
			$result .= sprintf("\n%s SQLSTATE: %d, code: %d.", $part["message"], $part["SQLSTATE"], $part["code"]);
		}
		return $result;
	}
	
	///////////////////////////////////////////////////////////////////////////
}

?>